home *** CD-ROM | disk | FTP | other *** search
/ PC World Komputer 2010 April / PCWorld0410.iso / pluginy Firefox / 14642 / 14642.xpi / chrome / modules / storage.js < prev    next >
Text File  |  2009-10-14  |  43KB  |  1,252 lines
  1. /* Copyright 2008, Boomtango.com.  All Rights Reserved. */
  2. /* storage.js
  3.  * Responsible for data storage
  4.  */
  5.  
  6. var EXPORTED_SYMBOLS = ["boomtangoStorage"];
  7.  
  8. var boomtangoStorage = {
  9.     DBVERSION: 1,
  10.     
  11.     /*
  12.         Returns a SQL conditional fragment based on starttime, endtime, types.  Output is expected to be 
  13.         sanitized.  Assumes 'types' is trusted and ensures starttime/endtime is an integer.
  14.     */
  15.     _buildWhereClause: function(starttime, endtime, types, skipTimes, dedupe){
  16.         var r = [];
  17.         var start = parseInt(starttime);
  18.         var end = parseInt(endtime);
  19.         
  20.         if(!skipTimes){
  21.             r.push( "(starttime>= ", start, " AND starttime<= ", end, ")");
  22.         }
  23.         if(types){
  24.             r.push(r.length ? " AND (" : " (");
  25.             
  26.             var len = types.length;
  27.             for(var x = 0; x < len; ){
  28.                 r.push("type = '", types[x], "'");
  29.                 x++;
  30.                 if(x < len){
  31.                     r.push(" OR ");
  32.                 }
  33.             }
  34.             r.push(")");
  35.         }
  36.  
  37.         if(dedupe){
  38.             var where;
  39.             r.push(r.length ? " AND (" : " (");
  40.             r.push("ftsrowid not in (SELECT ftsrowid FROM tracker WHERE ");
  41.             where = this._buildWhereClause(start, end, null, skipTimes, false);
  42.             if (where) {
  43.                 r.push(where + " AND ");
  44.             }
  45.             r.push("type != 'web')");
  46.  
  47.             r.push(")");
  48.         }
  49.         
  50.         return r.join("");
  51.     },
  52.     deleteAllThumb: function() {
  53.        this.app.log("storage::deleteAllThumb: ");
  54.        var sql = "DELETE FROM thumb;VACUUM;";
  55.        this.conn.executeSimpleSQL(sql);
  56.     },
  57.     updateThumb: function(id, data){
  58.         var now = Date.now();
  59.         var sql = ["UPDATE thumb SET data = ",
  60.             "'", data, "' , createtime = ", now, " WHERE ",
  61.             "id = ",id,
  62.             ].join("");
  63.         this.conn.executeSimpleSQL(sql);
  64.         
  65.     },
  66.     getThumbID: function(url){
  67.         var res = -1;
  68.         var sql = ["SELECT ",
  69.             "id,",
  70.             " createtime",
  71.             " FROM thumb WHERE url = ?1"
  72.             ].join("");
  73.         var statement = this.conn.createStatement(sql);
  74.         
  75.         statement.bindUTF8StringParameter(0, url);
  76.         
  77.         if (statement.executeStep()) {
  78.             res = {
  79.                 id: statement.getInt64(0),
  80.                 createtime: statement.getInt64(1)
  81.             };
  82.         }
  83.         
  84.         if(res.id === undefined){
  85.             res = { id: -1 };
  86.         }
  87.         statement.reset();
  88.         
  89.         return res;
  90.     },
  91.     getBlacklist: function(allrecs){
  92.         this.app.log("storage::getBlacklist");
  93.         var sql = ["SELECT ",
  94.             "data",
  95.             " FROM blacklist",
  96.             (allrecs ? "" : " WHERE internalOnly = 0")
  97.             ].join("");
  98.         var statement = this.conn.createStatement(sql);
  99.         
  100.         var res = [];
  101.         while (statement.executeStep()) {
  102.             res.push(statement.getString(0));
  103.         }
  104.         
  105.         this.app.debug("Num Results: " + res.length);
  106.         statement.reset();
  107.  
  108.         return res;
  109.     },
  110.     addBlacklistData: function(data, internalOnly){ 
  111.         this.app.log("storage::addBlacklistData (" + data + ")"); 
  112.         var sql = ["INSERT INTO blacklist (",
  113.                                     "data, ",
  114.                                     "internalOnly",
  115.                                     ") VALUES (",
  116.                                     "?1, ",
  117.                                     "?2 ",
  118.                                     ")"
  119.                                     ].join('');
  120.         
  121.         var statement = this.conn.createStatement(sql);
  122.         statement.bindUTF8StringParameter(0, data);
  123.         statement.bindInt64Parameter(1, internalOnly);
  124.         statement.execute();
  125.         statement.reset(); 
  126.         this.app.log("storage::addBlacklistData result = " + this.conn.lastInsertRowID + ")");
  127.         return this.conn.lastInsertRowID;
  128.     },
  129.     deleteBlacklistData: function(data){
  130.         var sql = "DELETE FROM blacklist WHERE data = ?1";
  131.         var statement = this.conn.createStatement(sql);
  132.         statement.bindUTF8StringParameter(0, data);
  133.         statement.execute();
  134.         statement.reset();
  135.         this.app.log("deleteBlacklistData: " + data);
  136.     },
  137.     // XXX: THIS DELETS ALL BLACKLIST URLS.  ONLY USED WHEN USER SPECIFIES INTENT
  138.     deleteAllBlacklist: function(){
  139.        this.app.log("storage::deleteAllBlacklist");
  140.        var sql = "DELETE FROM blacklist WHERE internalOnly = 0";
  141.        this.conn.executeSimpleSQL(sql);
  142.     },
  143.     addThumb: function(url, thumb){
  144.         this.app.log("storage::addThumb (" + url + ")");
  145.         var now = Date.now();
  146.         var sql = ["INSERT INTO thumb (",
  147.                                     "url,",
  148.                                     "createtime,",
  149.                                     "data",
  150.                                     ") VALUES (",
  151.                                     "?1, ",
  152.                                     "?2, ",
  153.                                     "?3 ",
  154.                                     ")"
  155.                                     ].join('');
  156.         
  157.         var statement = this.conn.createStatement(sql);
  158.         statement.bindUTF8StringParameter(0, url);
  159.         statement.bindInt64Parameter(1, now);
  160.         statement.bindUTF8StringParameter(2, thumb);
  161.         statement.execute();
  162.         statement.reset(); 
  163.         return this.conn.lastInsertRowID;
  164.     },
  165.     getThumb: function(id){
  166.         var res = "";
  167.         var is_id = typeof id == "number";
  168.         var sql = ["SELECT ",
  169.             "data",
  170.             " FROM thumb WHERE",
  171.             (is_id? " id = ?1" : " url = ?1")
  172.             ].join("");
  173.             
  174.         var statement = this.conn.createStatement(sql);
  175.         
  176.         if (is_id) {
  177.             statement.bindInt64Parameter(0, id);
  178.         }
  179.         else {
  180.             statement.bindUTF8StringParameter(0, id);
  181.         }
  182.         
  183.         if (statement.executeStep()) {
  184.             res = statement.getString(0);
  185.         }
  186.         
  187.         statement.reset();
  188.  
  189.         return res;
  190.     },
  191.  
  192.     querySummary: function(starttime, endtime){
  193.         var res = {};
  194.  
  195.         this.app.log("storage::querySummary");
  196.  
  197.         // get sums by type
  198.         var sql = ["SELECT ",
  199.             "sum(endtime - starttime), ",
  200.             "count(*), ",
  201.             "type FROM tracker WHERE ",
  202.             this._buildWhereClause(starttime, endtime),
  203.             " AND (endtime > 0)", 
  204.             " GROUP BY type"].join("");
  205.         
  206.         var statement = this.conn.createStatement(sql);
  207.         
  208.         res.types = [];
  209.         while (statement.executeStep()) {
  210.             res.types.push(
  211.                 {
  212.                 type: statement.getString(2),
  213.                 timespent: statement.getInt64(0),
  214.                 count: statement.getInt64(1)
  215.                 }
  216.              );
  217.         }
  218.         statement.reset();
  219.         
  220.         var types = res.types;
  221.         var len = types.length;
  222.         res.urls = {};
  223.         for(var x = 0; x < len; x++){
  224.             var type = types[x].type;
  225.             res.urls[type] = [];
  226.         
  227.             var sql = ["SELECT ",
  228.                 "sum(endtime - starttime), ",
  229.                 "type, ",
  230.                 "title, ",
  231.                 "ftsrowid, ",
  232.                 "id, ",
  233.                 "url FROM tracker WHERE ",
  234.                 this._buildWhereClause(starttime, endtime,[type]),
  235.                 " AND (endtime > 0)",
  236.                 " GROUP BY url ORDER BY sum(endtime - starttime) DESC LIMIT 5"].join("");
  237.         
  238.             var statement = this.conn.createStatement(sql);
  239.         
  240.             var a = res.urls[type];
  241.             while (statement.executeStep()) {
  242.                 a.push(
  243.                     {
  244.                     url: statement.getString(5),
  245.                     type: statement.getString(1),
  246.                     title: statement.getString(2),
  247.                     ftsrowid: statement.getInt64(3),
  248.                     id: statement.getInt64(4),
  249.                     timespent: statement.getInt64(0),
  250.                     }
  251.                 );
  252.             }
  253.             statement.reset();
  254.         }
  255.  
  256.         return res;
  257.     },
  258.     queryTrackerByTrackerID: function(id){
  259.         var res = [];
  260.         this.app.log("storage::queryTrackerByTrackerID");
  261.         var sql = ["SELECT ",
  262.             "url,",
  263.             "title,",
  264.             "type,",
  265.             "starttime,",
  266.             "endtime,",
  267.             "ftsrowid,",
  268.             "id,",
  269.             "preview FROM tracker WHERE id = ?1"
  270.             ].join("");
  271.         var statement = this.conn.createStatement(sql);
  272.        
  273.         statement.bindInt64Parameter(0, id);
  274.         
  275.  
  276.         if (statement.executeStep()) {
  277.             res.push(
  278.                 {
  279.                 url: statement.getString(0),
  280.                 title: statement.getString(1),
  281.                 type: statement.getString(2),
  282.                 endtime: statement.getInt64(4),
  283.                 starttime: statement.getInt64(3),
  284.                 ftsrowid: statement.getInt64(5),
  285.                 id: statement.getInt64(6),
  286.                 preview: statement.getString(7)
  287.                 }
  288.              );
  289.         } else {
  290.             return [];
  291.         }
  292.         
  293.         statement.reset();
  294.  
  295.         // also grab the web version
  296.         var sql = ["SELECT ",
  297.             "url,",
  298.             "title,",
  299.             "type,",
  300.             "starttime,",
  301.             "endtime,",
  302.             "ftsrowid,",
  303.             "id,",
  304.             "preview FROM tracker WHERE ftsrowid = ?1",
  305.             " AND type = 'web'"
  306.             ].join("");
  307.         
  308.         var statement = this.conn.createStatement(sql);
  309.         
  310.         statement.bindInt64Parameter(0, res[0].ftsrowid);
  311.  
  312.         while (statement.executeStep()) {
  313.             res.push(
  314.                 {
  315.                 url: statement.getString(0),
  316.                 title: statement.getString(1),
  317.                 type: statement.getString(2),
  318.                 endtime: statement.getInt64(4),
  319.                 starttime: statement.getInt64(3),
  320.                 ftsrowid: statement.getInt64(5),
  321.                 id: statement.getInt64(6),
  322.                 preview: statement.getString(7)
  323.                 }
  324.              );
  325.         }
  326.         return res;
  327.     },
  328.     queryTrackerByFTSRowId: function(id){
  329.         var res = [];
  330.         this.app.log("storage::queryTrackerByFTSRowId");
  331.         var sql = ["SELECT ",
  332.             "url,",
  333.             "title,",
  334.             "type,",
  335.             "starttime,",
  336.             "endtime,",
  337.             "ftsrowid,",
  338.             "id,",
  339.             "preview FROM tracker WHERE ftsrowid = ?1"
  340.             ].join("");
  341.         
  342.         var statement = this.conn.createStatement(sql);
  343.         
  344.         statement.bindInt64Parameter(0, id);
  345.  
  346.         while (statement.executeStep()) {
  347.             res.push(
  348.                 {
  349.                 url: statement.getString(0),
  350.                 title: statement.getString(1),
  351.                 type: statement.getString(2),
  352.                 endtime: statement.getInt64(4),
  353.                 starttime: statement.getInt64(3),
  354.                 ftsrowid: statement.getInt64(5),
  355.                 id: statement.getInt64(6),
  356.                 preview: statement.getString(7)
  357.                 }
  358.              );
  359.         }
  360.         
  361.         statement.reset();
  362.  
  363.         return res;
  364.     },
  365.     _queryTrackerBuildQuery: function(starttime, endtime, types, query, dedupe, getcount){
  366.         var selectfields;
  367.         if(getcount){
  368.             selectfields = "count(*)";
  369.  
  370.         } else {
  371.             selectfields = [
  372.                 "url,",
  373.                 "title,",
  374.                 "type,",
  375.                 "starttime,",
  376.                 "endtime,",
  377.                 "ftsrowid,",
  378.                 "id,",
  379.                 "preview"].join('');
  380.  
  381.         }
  382.         if(query){
  383.             return ["SELECT ",
  384.                 selectfields,
  385.                 " FROM tracker WHERE ftsrowid IN (",
  386.                 "SELECT  id FROM historyfts WHERE (starttime>=",
  387.                     starttime, " AND  starttime <=", endtime,
  388.                     ") AND historyfts MATCH :query) AND ",
  389.                     this._buildWhereClause(starttime, endtime, types, true, dedupe)].join("");
  390.         } 
  391.  
  392.         return ["SELECT ",
  393.             selectfields,
  394.             " FROM tracker WHERE ",
  395.             this._buildWhereClause(starttime, endtime, types, false, dedupe)].join("");
  396.     },
  397.     FTSIDToURL: function(id){
  398.         var sql = "SELECT url FROM tracker WHERE ftsrowid = ?1 LIMIT 1";
  399.         
  400.         var statement = this.conn.createStatement(sql);
  401.     
  402.         statement.bindInt64Parameter(0, id);
  403.         
  404.         if (statement.executeStep()) {
  405.             return statement.getString(0);
  406.         }
  407.         return "";
  408.     },
  409.     IDToURL: function(id){
  410.         var sql = "SELECT url FROM tracker WHERE id = ?1";
  411.         var statement = this.conn.createStatement(sql);
  412.     
  413.         statement.bindInt64Parameter(0, id);
  414.     
  415.         if (statement.executeStep()) {
  416.             return statement.getString(0);
  417.         }
  418.         return "";
  419.     },
  420.     IDToTitle: function(id){
  421.         var sql = "SELECT title FROM tracker WHERE id = ?1";
  422.         var statement = this.conn.createStatement(sql);
  423.     
  424.         statement.bindInt64Parameter(0, id);
  425.     
  426.         if (statement.executeStep()) {
  427.             return statement.getString(0);
  428.         }
  429.         return " ";
  430.     },
  431.     queryTrackerByID: function(id, order, offset, max, getcount, desc, types, starttime, endtime){
  432.         this.app.log("storage::queryTrackerByID");
  433.         
  434.         var whereclause = "";
  435.         if(starttime > 0){
  436.             whereclause += " AND (starttime >= " + parseInt(starttime) + ")";
  437.         }
  438.         if(endtime > 0){
  439.             whereclause += " AND (starttime <= " + parseInt(endtime) + ")";
  440.         }
  441.  
  442.         var r = [];
  443.         r.push(" AND (");
  444.         
  445.         var len = types.length;
  446.         for(var x = 0; x < len; ){
  447.             r.push("type = '", types[x], "'");
  448.             x++;
  449.             if(x < len){
  450.                 r.push(" OR ");
  451.             }
  452.         }
  453.         r.push(")");
  454.         var typeclause = r.join("");
  455.         var res = { totalcount: 0, data: [] };
  456.         var url = this.IDToURL(id);
  457.         if(!url.length){
  458.             return res;
  459.         }
  460.  
  461.         // first get the count
  462.         if(getcount){
  463.             var sql = ["SELECT count(*) FROM tracker WHERE ",
  464.                 "url = ?1", whereclause, typeclause].join("");
  465.             var statement = this.conn.createStatement(sql);
  466.             
  467.             statement.bindUTF8StringParameter(0, url);
  468.             
  469.             if (statement.executeStep()) {
  470.                 res.totalcount = statement.getInt64(0);
  471.             }
  472.  
  473.             statement.reset();
  474.  
  475.             this.app.debug("Total: " + res.totalcount);
  476.         }
  477.  
  478.         // clean order
  479.         switch(order){
  480.             case 'timespent':
  481.                 order = '(endtime - starttime)';
  482.                 break;
  483.             case 'title':
  484.             case 'url':
  485.             case 'starttime':
  486.             case 'type':
  487.                 break;
  488.             default:
  489.                 order = 'starttime';
  490.                 break;
  491.         } 
  492.             
  493.         // now get the data
  494.         var sql = ["SELECT ",
  495.                 "url,",
  496.                 "title,",
  497.                 "type,",
  498.                 "starttime,",
  499.                 "endtime,",
  500.                 "ftsrowid,",
  501.                 "id,",
  502.                 "preview FROM tracker WHERE ",
  503.                 "url = ?1",whereclause, typeclause,
  504.                 " ORDER BY ", order, (desc ? " DESC" : ""),
  505.                 " LIMIT ", max,
  506.                 " OFFSET ", offset].join("");
  507.         var statement = this.conn.createStatement(sql);
  508.         
  509.         statement.bindUTF8StringParameter(0, url);
  510.         
  511.         while (statement.executeStep()) {
  512.             res.data.push(
  513.                 {
  514.                 url: statement.getString(0),
  515.                 title: statement.getString(1),
  516.                 type: statement.getString(2),
  517.                 starttime: statement.getInt64(3),
  518.                 endtime: statement.getInt64(4),
  519.                 ftsrowid: statement.getInt64(5),
  520.                 id: statement.getInt64(6),
  521.                 preview: statement.getString(7)
  522.                 }
  523.              );
  524.         }
  525.         
  526.         this.app.debug("Num Results: " + res.data.length);
  527.         statement.reset();
  528.  
  529.         return res;
  530.     },
  531.     queryTrackerByMoreItems: function(type, starttime, endtime, order, offset, max, getcount, desc){
  532.         var res = { totalcount: 0, data: [] };
  533.         this.app.log("storage::queryTrackerByMoreItems");
  534.  
  535.         // first get the count
  536.         if(getcount){
  537.             var sql = ["SELECT id FROM tracker WHERE ",
  538.                 this._buildWhereClause(starttime, endtime, [type], false, false),
  539.                 " GROUP BY url, preview"
  540.                 ].join("");
  541.             var sql2 = ["SELECT COUNT(*) FROM (",
  542.                         sql,
  543.                         ")"
  544.                         ].join("");
  545.             var statement = this.conn.createStatement(sql2);
  546.         
  547.             if (statement.executeStep()) {
  548.                 res.totalcount = statement.getInt64(0);
  549.             }
  550.  
  551.             statement.reset();
  552.  
  553.             this.app.debug("Total: " + res.totalcount);
  554.         }
  555.  
  556.         // clean order
  557.         switch(order){
  558.             case 'timespent':
  559.                 order = '(endtime - starttime)';
  560.                 break;
  561.             case 'title':
  562.             case 'url':
  563.             case 'starttime':
  564.             case 'type':
  565.                 break;
  566.             default:
  567.                 order = 'starttime';
  568.                 break;
  569.         } 
  570.             
  571.         // now get the data
  572.         var sql = ["SELECT ",
  573.                 "url,",
  574.                 "title,",
  575.                 "type,",
  576.                 "starttime,",
  577.                 "endtime,",
  578.                 "ftsrowid,",
  579.                 "id,",
  580.                 "preview FROM tracker WHERE ",
  581.                 this._buildWhereClause(starttime, endtime, [type], false, false),
  582.                 " GROUP BY url, preview",
  583.                 " ORDER BY ", order, (desc ? " DESC" : ""),
  584.                 " LIMIT ", max,
  585.                 " OFFSET ", offset].join("");
  586.         var statement = this.conn.createStatement(sql);
  587.         while (statement.executeStep()) {
  588.             res.data.push(
  589.                 {
  590.                 url: statement.getString(0),
  591.                 title: statement.getString(1),
  592.                 type: statement.getString(2),
  593.                 starttime: statement.getInt64(3),
  594.                 endtime: statement.getInt64(4),
  595.                 ftsrowid: statement.getInt64(5),
  596.                 id: statement.getInt64(6),
  597.                 preview: statement.getString(7)
  598.                 }
  599.              );
  600.         }
  601.         
  602.         this.app.debug("Num Results: " + res.data.length);
  603.         statement.reset();
  604.  
  605.         return res;
  606.     },
  607.     _cleanQuery: function(query){
  608.         // sqlite crashes when there is a + or - in a match
  609.         return query.replace(/[+-]/g,' ');
  610.     },
  611.     queryTrackerBySERP: function(query){
  612.         var res = { totalcount: 0, data: [] };
  613.         this.app.log("storage::queryTrackerBySERP");
  614.         query = this._cleanQuery(query);
  615.  
  616.         // first get the count
  617.         var sql = "SELECT count(DISTINCT url) FROM historyfts WHERE historyfts MATCH ?1 AND url NOT LIKE '%google.%'";
  618.         
  619.         var statement = this.conn.createStatement(sql);
  620.         
  621.         statement.bindUTF8StringParameter(0, query);
  622.         
  623.         if (statement.executeStep()) {
  624.             res.totalcount = statement.getInt64(0);
  625.         }
  626.  
  627.         statement.reset();
  628.  
  629.         this.app.debug("Total: " + res.totalcount);
  630.  
  631.         // now get the data
  632.         var sql = ["SELECT ",
  633.                 "url,",
  634.                 "title,",
  635.                 "type,",
  636.                 "starttime,",
  637.                 "endtime,",
  638.                 "ftsrowid,",
  639.                 "id,",
  640.                 "preview FROM tracker WHERE ",
  641.                 "ftsrowid IN (",
  642.                 "SELECT  id FROM historyfts WHERE historyfts MATCH ?1 AND url NOT LIKE '%google.%')",
  643.                 "GROUP BY url",
  644.                 " ORDER BY starttime DESC",
  645.                 " LIMIT 3",
  646.                 " OFFSET 0"].join("");
  647.         var statement = this.conn.createStatement(sql);
  648.         
  649.         statement.bindUTF8StringParameter(0, query);
  650.         
  651.         while (statement.executeStep()) {
  652.             res.data.push(
  653.                 {
  654.                 url: statement.getString(0),
  655.                 title: statement.getString(1),
  656.                 type: statement.getString(2),
  657.                 starttime: statement.getInt64(3),
  658.                 endtime: statement.getInt64(4),
  659.                 ftsrowid: statement.getInt64(5),
  660.                 id: statement.getInt64(6),
  661.                 preview: statement.getString(7)
  662.                 }
  663.              );
  664.         }
  665.         
  666.         this.app.debug("Num Results: " + res.data.length);
  667.         statement.reset();
  668.  
  669.         return res;
  670.     },
  671.     queryTrackerBySearch: function(query, order, offset, max, getcount, desc, types, starttime, endtime){
  672.         var res = { totalcount: 0, data: [] };
  673.         this.app.log("storage::queryTrackerBySearch");
  674.  
  675.         query = this._cleanQuery(query);
  676.         var whereclause = "";
  677.         if(starttime > 0){
  678.             whereclause += " AND (starttime >= " + starttime + ")";
  679.         }
  680.         if(endtime > 0){
  681.             whereclause += " AND (starttime <= " + endtime + ")";
  682.         }
  683.  
  684.         var r = [];
  685.         r.push(" AND (");
  686.         
  687.         var len = types.length;
  688.         for(var x = 0; x < len; ){
  689.             r.push("type = '", types[x], "'");
  690.             x++;
  691.             if(x < len){
  692.                 r.push(" OR ");
  693.             }
  694.         }
  695.         r.push(")");
  696.         var typeclause = r.join("");
  697.         // first get the count
  698.         if(getcount){
  699.             var sql = ["SELECT count(DISTINCT title)",
  700.                         " FROM tracker WHERE ",
  701.                         "ftsrowid IN (",
  702.                         "SELECT  id FROM historyfts WHERE historyfts MATCH ?1)",
  703.                         whereclause, typeclause].join("");
  704.  
  705.             var statement = this.conn.createStatement(sql);
  706.             
  707.             statement.bindUTF8StringParameter(0, query);
  708.             
  709.             if (statement.executeStep()) {
  710.                 res.totalcount = statement.getInt64(0);
  711.             }
  712.  
  713.             statement.reset();
  714.  
  715.             this.app.debug("Total: " + res.totalcount);
  716.         }
  717.  
  718.         // clean order
  719.         switch(order){
  720.             case 'timespent':
  721.                 order = '(endtime - starttime)';
  722.                 break;
  723.             case 'title':
  724.             case 'url':
  725.             case 'starttime':
  726.             case 'type':
  727.                 break;
  728.             default:
  729.                 order = 'starttime';
  730.                 break;
  731.         } 
  732.             
  733.         // now get the data
  734.         var sql = ["SELECT ",
  735.                 "url,",
  736.                 "title,",
  737.                 "type,",
  738.                 "starttime,",
  739.                 "endtime,",
  740.                 "ftsrowid,",
  741.                 "id,",
  742.                 "preview FROM tracker WHERE ",
  743.                 "ftsrowid IN (",
  744.                 "SELECT  id FROM historyfts WHERE historyfts MATCH ?1)",
  745.                 whereclause, typeclause,
  746.                 " GROUP BY title ",
  747.                 " ORDER BY ", order, (desc ? " DESC" : ""),
  748.                 " LIMIT ?3",
  749.                 " OFFSET ?4"].join("");
  750.                 
  751.         var statement = this.conn.createStatement(sql);
  752.         
  753.         statement.bindUTF8StringParameter(0, query);
  754.         statement.bindUTF8StringParameter(1, order);
  755.         statement.bindInt64Parameter(2, max);
  756.         statement.bindInt64Parameter(3, offset);
  757.         
  758.         while (statement.executeStep()) {
  759.             res.data.push(
  760.                 {
  761.                 url: statement.getString(0),
  762.                 title: statement.getString(1),
  763.                 type: statement.getString(2),
  764.                 starttime: statement.getInt64(3),
  765.                 endtime: statement.getInt64(4),
  766.                 ftsrowid: statement.getInt64(5),
  767.                 id: statement.getInt64(6),
  768.                 preview: statement.getString(7)
  769.                 }
  770.              );
  771.         }
  772.         
  773.         this.app.debug("Num Results: " + res.data.length);
  774.         statement.reset();
  775.  
  776.         return res;
  777.     },
  778.     queryTrackerByCategory: function(starttime, endtime, types, query, max, dedupe){
  779.         var res = {};
  780.         this.app.log("storage::queryTrackerByCategory: " + query);
  781.         var len = types.length;
  782.         for(var x = 0; x < len; x++){
  783.             var type = types[x];
  784.             
  785.             // builds a sql statement with :query as a bind parameter
  786.             var sql = this._queryTrackerBuildQuery(starttime, endtime, [type], query, dedupe);
  787.              
  788.             sql += "GROUP BY url, preview ORDER BY id DESC";
  789.             if(max){
  790.                 sql += " LIMIT " + max;
  791.             }
  792.         
  793.             var statement = this.conn.createStatement(sql);
  794.         
  795.             if (query) {
  796.                 var queryidx = statement.getParameterIndex(":query");
  797.                 statement.bindUTF8StringParameter(queryidx, query);
  798.             }
  799.             
  800.             while (statement.executeStep()) {
  801.                 var url = statement.getString(0);
  802.  
  803.                 if(!res.hasOwnProperty(type)){
  804.                     res[type] = [];
  805.                 }
  806.  
  807.                 res[type].push(
  808.                     {
  809.                     url: url,
  810.                     title: statement.getString(1),
  811.                     type: type,
  812.                     starttime: statement.getInt64(3),
  813.                     endtime: statement.getInt64(4),
  814.                     ftsrowid: statement.getInt64(5),
  815.                     preview: statement.getString(7),
  816.                     id: statement.getInt64(6)
  817.                     }
  818.                 );
  819.             }
  820.             
  821.             statement.reset();
  822.         }
  823.  
  824.         return res;
  825.     },
  826.     queryTrackerByThumbnail: function(starttime, endtime, types, query, offset, max){
  827.         var res = [];
  828.         var total = 0;
  829.         this.app.log("storage::queryTracker: " + query);
  830.         
  831.         // first get count
  832.         var sql = this._queryTrackerBuildQuery(starttime, endtime, types, query, false, true);
  833.         var statement = this.conn.createStatement(sql);
  834.         
  835.         if (query) {
  836.             var queryidx = statement.getParameterIndex(":query");
  837.             statement.bindUTF8StringParameter(queryidx, query);
  838.         }
  839.  
  840.         if(statement.executeStep()){
  841.             total = statement.getInt64(0);
  842.         }
  843.         statement.reset();
  844.         
  845.         // next get our data
  846.         var sql = this._queryTrackerBuildQuery(starttime, endtime, types, query) +
  847.                 "ORDER BY starttime DESC LIMIT " + max + " OFFSET " + offset;
  848.         var statement = this.conn.createStatement(sql);
  849.         
  850.         if (query) {
  851.             var queryidx = statement.getParameterIndex(":query");
  852.             statement.bindUTF8StringParameter(queryidx, query);
  853.         }
  854.         while (statement.executeStep()) {
  855.             res.push(
  856.                 {
  857.                 url: statement.getString(0),
  858.                 title: statement.getString(1),
  859.                 type: statement.getString(2),
  860.                 starttime: statement.getInt64(3),
  861.                 endtime: statement.getInt64(4),
  862.                 ftsrowid: statement.getInt64(5),
  863.                 preview: statement.getString(7),
  864.                 id: statement.getInt64(6)
  865.                 }
  866.              );
  867.         }
  868.         
  869.         this.app.debug("Num Results: " + res.length);
  870.         statement.reset();
  871.  
  872.         return { total: total, data: res };
  873.     },
  874.     queryTracker: function(starttime, endtime, types, query){
  875.         var res = [];
  876.         this.app.log("storage::queryTracker: " + query);
  877.         
  878.         // builds a sql statement with :query as a bind parameter
  879.         var sql = this._queryTrackerBuildQuery(starttime, endtime, types, query);
  880.         
  881.         var statement = this.conn.createStatement(sql);
  882.         
  883.         if (query) {
  884.             var queryidx = statement.getParameterIndex(":query");
  885.             statement.bindUTF8StringParameter(queryidx, query);
  886.         }
  887.         
  888.         while (statement.executeStep()) {
  889.             res.push(
  890.                 {
  891.                 url: statement.getString(0),
  892.                 title: statement.getString(1),
  893.                 type: statement.getString(2),
  894.                 starttime: statement.getInt64(3),
  895.                 endtime: statement.getInt64(4),
  896.                 ftsrowid: statement.getInt64(5),
  897.                 preview: statement.getString(7),
  898.                 id: statement.getInt64(6)
  899.                 }
  900.              );
  901.         }
  902.         
  903.         this.app.debug("Num Results: " + res.length);
  904.         statement.reset();
  905.  
  906.         return res;
  907.     },
  908.     resetDB: function(){
  909.         this.app.debug("storage::resetDB");
  910.         // for now, delete any old dbs
  911.         var file = Components.classes["@mozilla.org/file/directory_service;1"]
  912.                              .getService(Components.interfaces.nsIProperties)
  913.                              .get("ProfD", Components.interfaces.nsIFile);
  914.         file.append("boomtango.sqlite");
  915.         try {
  916.             file.remove(false);
  917.         } catch(e){}
  918.     },
  919.     upgradeDB: function(){
  920.         this.app.debug("storage::upgradeDB to " + this.DBVERSION);
  921.         // for now, delete any old dbs
  922.         var file = Components.classes["@mozilla.org/file/directory_service;1"]
  923.                              .getService(Components.interfaces.nsIProperties)
  924.                              .get("ProfD", Components.interfaces.nsIFile);
  925.         file.append("boomtango.sqlite");
  926.         try {
  927.             file.remove(false);
  928.         } catch(e){}
  929.         this.app.dbversion = this.DBVERSION;
  930.     },
  931.     _fileCopy: function(fromFile, toFile) {
  932.         try {
  933.             // READ file (binary)
  934.             var istream = Components.classes["@mozilla.org/network/file-input-stream;1"].
  935.             createInstance(Components.interfaces.nsIFileInputStream);
  936.             istream.init(fromFile, -1, -1, false);
  937.             
  938.             var bstream = Components.classes["@mozilla.org/binaryinputstream;1"].
  939.             createInstance(Components.interfaces.nsIBinaryInputStream);
  940.             bstream.setInputStream(istream);
  941.             
  942.             var bytes = bstream.readBytes(bstream.available());
  943.             this.app.log("READ FILE LENGTH: " + bytes.length);
  944.             
  945.             // WRITE file (binary)
  946.             var ostream = Components.classes["@mozilla.org/network/safe-file-output-stream;1"].
  947.                             createInstance(Components.interfaces.nsIFileOutputStream);
  948.             ostream.init(toFile, 0x04 | 0x08 | 0x20, 0600, 0); // write, create, truncate
  949.             
  950.             ostream.write(bytes, bytes.length);
  951.             if (ostream instanceof Components.interfaces.nsISafeOutputStream) {
  952.                 ostream.finish();
  953.             } else {
  954.                 ostream.close();
  955.             }
  956.         }
  957.         catch (err) {
  958.             this.app.log("ERROR: " + err);
  959.             return false;
  960.         }
  961.         return true;
  962.     },
  963.     backupToFile: function(filename){
  964.         var backupfile = Components.classes["@mozilla.org/file/local;1"].
  965.                             createInstance(Components.interfaces.nsILocalFile);
  966.         backupfile.initWithPath(filename);
  967.     
  968.         var dbfile = Components.classes["@mozilla.org/file/directory_service;1"]
  969.                              .getService(Components.interfaces.nsIProperties)
  970.                              .get("ProfD", Components.interfaces.nsIFile);
  971.         dbfile.append("boomtango");
  972.         dbfile.append("data");
  973.         dbfile.append("boomtango.sqlite");
  974.         
  975.         return this._fileCopy(dbfile, backupfile);
  976.     },
  977.     validBackup: function(file){
  978.         try {
  979.             // check if we can open db
  980.             var storageService = Components.classes["@mozilla.org/storage/service;1"]
  981.                                 .getService(Components.interfaces.mozIStorageService);
  982.             var backupconn = storageService.openUnsharedDatabase(file);
  983.             
  984.             var sql = "select name from SQLite_Master";
  985.             var statement = backupconn.createStatement(sql);
  986.         
  987.             var res = [];
  988.             while (statement.executeStep()) {
  989.                 // create a hash
  990.                 res[statement.getString(0)] = true;
  991.                 this.app.log("VALIDATE:" +  statement.getString(0));
  992.             }
  993.             
  994.             // verify existance of required tables
  995.             var required_tables = ["tracker",
  996.                                     "thumb",
  997.                                     "blacklist"];
  998.             
  999.         
  1000.             this.app.debug("Num Results: " + res.length);
  1001.  
  1002.             for (x in required_tables) {
  1003.                 if (!res[required_tables[x]]) {
  1004.                     this.app.log('cant find ' + required_tables[x]);
  1005.                     return false;
  1006.                 }
  1007.             }
  1008.             statement.reset();
  1009.         }
  1010.         catch (ex) {
  1011.             return false;
  1012.         }
  1013.         
  1014.         return true;
  1015.     },
  1016.     restoreFromFile: function(filename){
  1017.         var backupfile = Components.classes["@mozilla.org/file/local;1"].
  1018.                             createInstance(Components.interfaces.nsILocalFile);
  1019.         backupfile.initWithPath(filename);
  1020.     
  1021.         var dbfile = Components.classes["@mozilla.org/file/directory_service;1"]
  1022.                              .getService(Components.interfaces.nsIProperties)
  1023.                              .get("ProfD", Components.interfaces.nsIFile);
  1024.         dbfile.append("boomtango");
  1025.         dbfile.append("data");
  1026.         dbfile.append("boomtango.sqlite");
  1027.         
  1028.         if (!this.validBackup(backupfile)) {
  1029.             return false;
  1030.         }
  1031.         
  1032.         this._fileCopy(backupfile, dbfile);
  1033.         
  1034.         // re-initialize
  1035.         
  1036.         var file = Components.classes["@mozilla.org/file/directory_service;1"]
  1037.                              .getService(Components.interfaces.nsIProperties)
  1038.                              .get("ProfD", Components.interfaces.nsIFile);
  1039.         file.append("boomtango");
  1040.         file.append("data");
  1041.         file.append("boomtango.sqlite");
  1042.  
  1043.         this.app.log("dbversion=" + this.DBVERSION);
  1044.         this.app.log("current version=" + this.app.dbversion);
  1045.  
  1046.         if(this.app.dbversion < this.DBVERSION){
  1047.             this.upgradeDB();
  1048.         }
  1049.         var storageService = Components.classes["@mozilla.org/storage/service;1"]
  1050.                                 .getService(Components.interfaces.mozIStorageService);
  1051.                                 
  1052.         this.conn = storageService.openUnsharedDatabase(file);
  1053.         this.app.debug("storage::re-init");
  1054.         this.app.reloadTabsWithAttribute("boomtangoHistory");
  1055.         
  1056.         return true;
  1057.     },
  1058.     init: function(app){
  1059.         this.app = app;
  1060.         var file = Components.classes["@mozilla.org/file/directory_service;1"]
  1061.                              .getService(Components.interfaces.nsIProperties)
  1062.                              .get("ProfD", Components.interfaces.nsIFile);
  1063.         file.append("boomtango");
  1064.         file.append("data");
  1065.         file.append("boomtango.sqlite");
  1066.  
  1067.         this.app.log("dbversion=" + this.DBVERSION);
  1068.         this.app.log("current version=" + this.app.dbversion);
  1069.         
  1070.         if(this.app.killBTData){
  1071.             this.app.killBTData = false;
  1072.             this.resetDB();
  1073.         } else if(this.app.dbversion < this.DBVERSION){
  1074.             this.upgradeDB();
  1075.         }
  1076.         var storageService = Components.classes["@mozilla.org/storage/service;1"]
  1077.                                 .getService(Components.interfaces.mozIStorageService);
  1078.         var newdb = !file.exists();
  1079.         this.conn = storageService.openUnsharedDatabase(file);
  1080.         this.app.debug("storage::init");
  1081.         
  1082.         if(newdb){
  1083.             this.initTables();
  1084.             this.loadFromHistory();
  1085.         }
  1086.         this.init = function(){};
  1087.     },
  1088.     loadFromHistory: function(){
  1089.         this.app.debug("storage::loadFromHistory");
  1090.         this.app.newUser = true;
  1091.     },
  1092.     cleanupDB: function(){
  1093.         var lastcleanup = this.app.lastcleanup;
  1094.         var lastshutdown = this.app.lastshutdown;
  1095.         var self = this;
  1096.         
  1097.         // write our current time each minute in case of crash
  1098.         var callback = {
  1099.             notify: function(){
  1100.                 var d = new Date();
  1101.                 self.app.lastshutdown = d.getTime();
  1102.             }
  1103.         };
  1104.         var timer = Components.classes["@mozilla.org/timer;1"]
  1105.             .createInstance(Components.interfaces.nsITimer);
  1106.         timer.initWithCallback(callback, 60*1000, Components.interfaces.nsITimer.TYPE_REPEATING_SLACK);
  1107.         callback.notify();
  1108.  
  1109.         var query = ["UPDATE tracker SET endtime=",
  1110.             lastshutdown, " WHERE ",
  1111.             "starttime > ", lastcleanup, " AND endtime < 0"
  1112.             ].join('');
  1113.         this.conn.executeSimpleSQL(query);
  1114.  
  1115.         var now = new Date();
  1116.         this.app.lastcleanup = now.getTime();
  1117.     },
  1118.     initTables: function() {
  1119.         this.app.debug("storage::inittables");
  1120.         this.conn.executeSimpleSQL("create virtual table historyfts using fts3(title TEXT,content TEXT, url TEXT, starttime INTEGER, endtime INTEGER, id INTEGER);");
  1121.  
  1122.         // Setup thumb table
  1123.         this.conn.executeSimpleSQL(["CREATE TABLE IF NOT EXISTS thumb (",
  1124.                                     "id INTEGER PRIMARY KEY,",
  1125.                                     "url TEXT,",
  1126.                                     "createtime INTEGER,",
  1127.                                     "data TEXT)"
  1128.                                     ].join(''));
  1129.         this.conn.executeSimpleSQL(["CREATE INDEX IF NOT EXISTS thumb_url_index ",
  1130.                                     "ON thumb (url)"
  1131.                                     ].join(''));
  1132.         // Setup blacklist table
  1133.         this.conn.executeSimpleSQL(["CREATE TABLE IF NOT EXISTS blacklist (",
  1134.                                     "id INTEGER PRIMARY KEY,",
  1135.                                     "internalOnly INTEGER,",
  1136.                                     "data TEXT)"
  1137.                                     ].join(''));
  1138.  
  1139.         this.addBlacklistData("chrome://*", 1);
  1140.         this.addBlacklistData("about:*", 1);
  1141.         // Setup tracker table
  1142.         this.conn.executeSimpleSQL(["CREATE TABLE IF NOT EXISTS tracker (",
  1143.                                     "id INTEGER PRIMARY KEY,",
  1144.                                     "url TEXT,",
  1145.                                     "title TEXT,",
  1146.                                     "type TEXT,",
  1147.                                     "ftsrowid INTEGER,",
  1148.                                     "starttime INTEGER,",
  1149.                                     "endtime INTEGER,",  
  1150.                                     "preview TEXT)"
  1151.                                     ].join(''));
  1152.         this.conn.executeSimpleSQL(["CREATE INDEX IF NOT EXISTS ftsrowid_index ",
  1153.                                     "ON tracker (ftsrowid)"
  1154.                                     ].join(''));
  1155.         this.conn.executeSimpleSQL(["CREATE INDEX IF NOT EXISTS starttime_index ",
  1156.                                     "ON tracker (starttime)"
  1157.                                     ].join(''));
  1158.     },
  1159.     addFTS: function(title, content, starttime, endtime, url){
  1160.         this.app.log("storage::addFTS: " + title);
  1161.         this.app.log("(" + url + ")");
  1162.         var sql = ["INSERT INTO historyfts (",
  1163.                                     "title,",
  1164.                                     "content, url, starttime, endtime, id) VALUES (",
  1165.                                     "?1, ",
  1166.                                     "?2, ?3, ?4, ?5, ?6)"
  1167.                                     ].join('');
  1168.         var statement = this.conn.createStatement(sql);
  1169.         var result = this.app.getNextID();
  1170.         statement.bindUTF8StringParameter(0, title);
  1171.         statement.bindUTF8StringParameter(1, content);
  1172.         statement.bindUTF8StringParameter(2, url);
  1173.         statement.bindInt64Parameter(3, starttime);
  1174.         statement.bindInt64Parameter(4, endtime);
  1175.         statement.bindInt64Parameter(5, result);
  1176.         statement.execute();
  1177.         statement.reset();
  1178.  
  1179.         return result;
  1180.     },
  1181.     /* Tracker Commands */
  1182.     addTrackers: function(url, title, ftsrowid, starttime, endtime, trackers){
  1183.         this.app.log("storage::addTrackers: " + title + " (" + trackers.length + ")");
  1184.         if(trackers){
  1185.             var len = trackers.length;
  1186.             while(len--){
  1187.                 var trackertitle = trackers[len].title || title;
  1188.                 this._addTracker(url, trackertitle, ftsrowid, starttime, endtime, trackers[len]);
  1189.             }
  1190.         }
  1191.     },
  1192.     _addTracker: function(url, title, ftsrowid, starttime, endtime, tracker) {
  1193.         var sql = ["INSERT INTO tracker (",
  1194.                                     "url,",
  1195.                                     "title,",
  1196.                                     "type,",
  1197.                                     "starttime,",
  1198.                                     "endtime,",
  1199.                                     "ftsrowid,",
  1200.                                     "preview) VALUES (",
  1201.                                     "?1, ",
  1202.                                     "?2, ",
  1203.                                     "?3, ",
  1204.                                     "?4, ",
  1205.                                     "?5, ",
  1206.                                     "?6, ",
  1207.                                     "?7)"
  1208.                                     ].join('');
  1209.         
  1210.         var statement = this.conn.createStatement(sql);
  1211.         statement.bindUTF8StringParameter(0, url);
  1212.         statement.bindUTF8StringParameter(1, title);
  1213.         statement.bindUTF8StringParameter(2, tracker.type);
  1214.         statement.bindInt64Parameter(3, starttime);
  1215.         statement.bindInt64Parameter(4, endtime);
  1216.         statement.bindUTF8StringParameter(5, ftsrowid);
  1217.         statement.bindUTF8StringParameter(6, tracker.preview);
  1218.         statement.execute();
  1219.         statement.reset();
  1220.  
  1221.         return this.conn.lastInsertRowID;
  1222.     },
  1223.     // XXX: THIS DELETS ALL TRACKERS.  ONLY USED WHEN USER SPECIFIES INTENT
  1224.     deleteAllTrackers: function(){
  1225.        this.app.log("storage::deleteAllTrackers");
  1226.        var sql = "DELETE FROM tracker";
  1227.        this.conn.executeSimpleSQL(sql);
  1228.     },
  1229.     deleteTrackersForFTSRowId: function(id){
  1230.         this.app.log("storage::deleteTrackersForFTSRowId: " + id);
  1231.         var sql = "DELETE FROM tracker WHERE ftsrowid = ?1";
  1232.        
  1233.         var statement = this.conn.createStatement(sql);
  1234.         
  1235.         statement.bindInt64Parameter(0, id);
  1236.         statement.executeStep();
  1237.         statement.reset();
  1238.     },
  1239.     deleteTrackersForURL: function(url){
  1240.        this.app.log("storage::deleteTrackersForURL: " + url);
  1241.        var sql = ["DELETE FROM tracker WHERE url = '",
  1242.             url, "'"].join("");
  1243.        this.conn.executeSimpleSQL(sql);
  1244.     },
  1245.     updateTrackerEnd: function(url, end, ftsrowid){
  1246.        var sql = ["UPDATE tracker SET endtime = ", end,  " WHERE url = '",
  1247.             url, "'", " AND endtime <  0"].join("");
  1248.        this.conn.executeSimpleSQL(sql);
  1249.  
  1250.     }
  1251. };
  1252.